MySQL 切换 ShardingSphere 的测试实践

作者：曾哲宇  编辑：毕小烦

应用项目作为 toB 基础授权系统，其特点是数据访问量大，单表数据量大，数据热度高。目前的数据库采用单点 MySQL 实例形式，虽当前阶段仅接入少量业务，但其数据增量远超预期，部分单表的数据量已达到 4亿以上。未来计划接入更多的业务，数据量将持续快速增长，现有的数据库单点部署形式将无法满足要求，因此计划引入 ShardingSphere 中间件，将单点部署形式升级为分库形式部署，以符合数据增长及性能的要求。

本文主要分享 ShardingSphere 在项目实际运用过程中的测试思路及经验总结。

一、ShardingSphere 简介

传统的将数据集中存储至单一数据节点的解决方案，在性能、可用性和运维成本这三方面已经难以满足互联网的海量数据场景。

从性能方面来说，由于关系型数据库大多采用 B+ 树类型的索引，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降；同时，高并发访问请求也使得集中式数据库成为系统的最大瓶颈。

从可用性的方面来讲，服务化的无状态型，能够达到较小成本的随意扩容，这必然导致系统的最终压力都落在数据库之上。而单一的数据节点，或者简单的主从架构，已经越来越难以承担。数据库的可用性，已成为整个系统的关键。

从运维成本方面考虑，当一个数据库实例中的数据量达到阈值以上，对于 DBA 的运维压力就会增大。数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控。一般来讲，单一数据库实例的数据量的阈值在1TB之内，是比较合理的范围。

在传统的关系型数据库无法满足互联网场景需要的情况下，将数据存储至原生支持分布式的 NoSQL 的尝试越来越多。但 NoSQL 对 SQL 的不兼容性以及生态圈的不完善，使得它们在与关系型数据库的博弈中始终无法完成致命一击，而关系型数据库的地位却依然不可撼动。

img

数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中以达到提升性能瓶颈以及可用性的效果。数据分片的有效手段是对关系型数据库进行分库和分表。分库和分表均可以有效的避免由数据量超过可承受阈值而产生的查询瓶颈。

除此之外，分库还能够用于有效的分散对数据库单点的访问量；分表虽然无法缓解数据库压力，但却能够提供尽量将分布式事务转化为本地事务的可能，一旦涉及到跨库的更新操作，分布式事务往往会使问题变得复杂。使用多主多从的分片方式，可以有效的避免数据单点，从而提升数据架构的可用性。

通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。数据分片的拆分方式又分为垂直分片和水平分片。

二、测试策略：前期准备

本次切换 ShardingSphere 测试目的是：结合线上真实场景，在未来数据量及用户增量情况下，探究 MySQL 更换 ShardingSphere 后是否可以满足业务需求。

2.1 ShardingSphere 侧：验证 SS 功能及性能是否满足业务需求

对 SS 功能及性能进行验证测试，分析是否满足业务接入需求。

① 验证 SS 对 SQL 的支持情况

验证 SS 方案下各类常用 SQL 的支持情况， SS 版本不同会有些许区别，官方文档中有提供支持的 SQL 语法，但是在实际测试过程中，还是存在一些官方未提及的情况，所以前期准备环节进行测试是有必要的。

场景用例

DML 语句	插入数据 insert	单条是否带列名是否带表名是否带单引号批量多个 value``insert into select分区键的值：固定值，表达式，函数普通字段的值：固定值，表达式，函数ON DUPLICATE KEY UPDATE``insert set
修改数据 update	修改分区键修改普通键带 where/不带 where带别名
删除数据 delete	带 where查询条件是分片键>,<,=``in,exists子查询>,<,between函数查询条件不是分片键不带 where
select	select 常用函数或系统参数表查询 select 语句：select,from,where,group by,having,order by,limit联合查询union all
DDL语句	库定义	创建删除修改
表定义	创建（字符集，主键，索引，约束，外键约束，自增，非空，唯一，默认值）删除修改（添加列，删除列，修改列，表信息修改）truncate
索引	创建删除修改
其他语句	给用户授权	create user root@'192.168.32.%' identified by '123'; grant all on *.* to root@'192.xx.32.%' create user ywx@'192.168.32.%' identified by '123'; grant update,select,delete,insert on wordpress.* to ywx@'192.xx.32.%';
回收用户权限	revoke delete on wordpress.* from user1@'192.xx.32.%';
show 命令
set	set foreign_key_constraint = 1;
reset
desc
事务控制语句

② 验证 SS 环境下，常用 SQL 性能压测

分别使用 SysBench 及 Springboot-mybatis 进行压测

SysBench

1. 测试目标：评估 ShardingSphere 性能基线指标，与 MySQL 进行对比。
2. 测试策略：使用 4 种不同配置。

• 直连 MySQL
• 只有一个分库分表
• 分 10 个库，分库策略为 order_id % 10，每个分库都在同一个 MySQL 实例上
• 分 10 个库，分库策略为 order_id % 10，分库分布在 4 个 MySQL 实例上

1. 测试场景：

• 单点 select
• 联合查询
• insert，update，delete 非事务
• insert 事务
• insert，delete 事务
• update 事务

1. 压测结果：

• 相同并发数下，只有一个分库分表和多个分库分表相比，前者 QPS 略高。
• 同样是 10 个分库的场景，分布在 1 个 MySQL 实例和分布在 4 个 MySQL 实例相比，QPS 差不多。
• 在并发数较小的情况下，直连 MySQL 和使用 ShardingSphere，QPS 相差很大，随着并发数增大，CPU 达到瓶颈，两者的 QPS 差距缩小。
• 在分库分表场景下，增大并发线程数，往往是 SharingSphere-proxy 的 CPU 先达到瓶颈，而 MySQL 还没饱和，因此，最大 QPS 往往达不到直连 MySQL 下的最大 QPS。两者相差几千到一万不等。
• 当并发数为 100 时，分库场景下压测报错，因此需要在 SDK 下重新进行 ShardingSphere 性能测试。

Springboot-mybatis

1. 测试目的：在上一次用 SysBench 评估 ShardingSphere 性能基线时，发现在事务的场景下，增大并发会导致 MySQL 连接数不足的问题，进一步分析发现，使用 SysBench 压测跟使用 JAVA 代码写压测脚本，两者在 Shardingsphere-proxy 上的表现不同。因此，本次使用 XX-mybatis 搭建测试工程执行 SQL 语句，再次对 ShardingSphere 进行性能测试。
2. 测试策略：根据上一次的性能测试结果，分库在同一个 MySQL 实例或者在不同 MySQL 实例上性能差异不大，因此本次只测试分 10 个库，分库策略为 order_id % 10，每个分库都在同一个 MySQL 实例上。

1. 测试场景：同 SysBench
2. 测试结果：

• select 的场景：proxy 集群的情况下，TPS 能达到单机情况下的两倍，大约在 2 万多，proxy cpu 达到瓶颈。通过扩展 proxy，可再增大 TPS。
• insert/update/delete 非事务场景，和 insert/delete 事务场景：proxy 单机的情况下 ，proxy cpu 成为瓶颈；集群的情况下，最大 TPS 跟直连 MySQL 差不多，在 3000 左右，MySQL CPU 成为瓶颈。
• update 事务场景（一个事务中包含两个 update 语句）：proxy 集群情况下， proxy cpu 和 MySQL CPU 基本都达到饱和，TPS 在 3000 左右。可通过扩展 proxy，增大 TPS。

2.2 应用侧：验证应用是否具备接入能力

1. 分析数据模型，确定各数据表分表规则，确定拆分 key。
2. 分析业务场景，对不支持的 SQL 语句进行优化。
3. 优化代码，将涉及的 SQL 带拆分 key。
4. 对以上调整做功能验证场景回归。
5. 准备第二套性能环境，应用对接 SS 后进行压测，确保性能指标符合预期。

三、测试策略：测试执行

从实施方案流程分析，本次应用数据库迁移 SS 主要验证点分别是：功能验证，数据验证，性能验证 3 个方面。其中功能验证在不同实施阶段要进行多次重复验证，所以测试策略的合理制定会提高 测试效率并降低上线失败的风险。

1. 功能验证

功能验证部分，系统对接 SS的改动点主要为 SQL 上的优化，其系统原业务功能逻辑未做调整，制定测试策略时着重按照 SQL 语法场景进行分类，同时对特异点进行验证，常规功能回归采用接口自动化覆盖。

具体实施内容：

1. 主流程功能场景全量回归。
2. 根据 SQL 场景增删改查对接口进行分类，对同语法的接口挑选部分做接口测试，其余使用接口自动化覆盖。
3. 特异点分析验证，例如：可能会大量传参的 in 查询，batchInsert，多线程 select，case when 等，对该类接口做指定场景的接口回归验证。
4. 大数据量下分页查询验证。
5. 自动化接口回归。

2. 数据验证

数据验证主要分为两个部分，为迁移至 SS，原数据模型会发生一定（可控）的变化，再就是在整体迁移过程中的多次迁移数据验证及数据同步验证。

具体实施内容：

1. 数据验证前后，对当前数据模型中的数据做数据归档，表 - 表行数 - 准备特定验证数据。
2. 数据迁移后，进行表行数对比。
3. 对准备的特定数据进行验证。
4. 随机抽样验证。
5. 数据同步位点丢失等异常场景下的数据恢复能力验证。

3. 性能验证

由于前期做了充分的性能测试预研工作，对于 SS 自身及应用对接后的性能指标均有了一份可靠的性能基线，在实施过程中，更多去关注线上 TOP 访问量接口的性能压测指标是否符合预期及扩展后是否可以满足业务增长需要即可。

具体实施内容：

1. 根据线上接口调用情况及功能验证环节分析出的 SQL 场景，制定性能测试场景范围。
2. 相同数据情况下，对比数据源分别为单体 MySQL 及 SS 的各项性能指标。
3. 模拟当前线上数据量成倍增长后，对比压测结果与当前性能指标，分析是否符合预期。
4. 关注其他性能问题及调优。

四、经验总结

前期准备较为充分，整体切换数据库较为顺利，但过程中还是遇到了一些问题（仅供参考，版本不同，场景不同结果也许会有出入）。

1. 使用第三方数据库工具连接 SS 时，部分软件（如 DataGrip，在设置中可关闭）会在查询时默认带 limit 条件，会污染整个连接源，导致后续的查询均默认带了 limit。
2. 使用 SS 数据库时，应用或个人在执行 SQL 时一定要在条件上带分表键，对于数据量大的场景，稍有不慎就会造成事故。（可以在中间件对异常 SQL 进行拦截，避免损失）
3. 如在线上由于不明原因导致出现重复数据，要准备完善的响应预案及数据修复脚本。
4. 由于 SS 为多库，每个库的连接数均可以单独配置，配置时应充分考虑用户连接数问题，当生产出现大量并发请求到其中某一个分库时，很容易由于设置不合理导致数据库挂掉。
5. 分析 SQL 场景时，不要放过任何不传分表键的场景，比如 if 场景下，不同判断分支是否有正确传入分表键。
6. 其他一些未覆盖的 SQL 语法场景，例如 select * from table a where id in(select id from table b …… 无法获取正确的结果集的情况，在需要使用子查询和连接查询的时候。需要完整确认关联表分片规则完全一致等等。
7. 由于本次业务系统切换 SS，是在已运行的生产实例进行，在测试阶段，要充分进行模拟演练以及回滚演练。本文由于关注点在测试策略制定上，未提及整体实施过程中关于演练部分的内容，后续有机会也会做相关分享。

推荐阅读

（完）

如果文章对你有帮助，记得留言、点赞、加关注哦！